import pandas as pd
import importlib
import plotly.graph_objects as go
import plotly.express as px
import plotly
from scipy.stats import shapiro
from scipy.stats import levene
from scipy.stats import mannwhitneyu
import warnings
from IPython.display import display, Markdown, HTML

# Graphing, helper stuff
import src.graph as graph
import src.stats_utils as stats_utils
import src.pg_query as pg_query
import src.queries as queries
import src.graph_utils as graph_utils

importlib.reload(pg_query)
importlib.reload(queries);
importlib.reload(graph_utils);

# Config Settings
plotly.offline.init_notebook_mode()
VERBOSE = False

display(HTML("<style>.container { width:480px !important; }</style>"))

if not VERBOSE:
    warnings.filterwarnings("ignore")

reviews_col_names = queries.get_col_names(pg_query.engine)
if VERBOSE:
    display(reviews_col_names)

# TODO: ADD SUMMARY DATA LIKE
# FOR EACH TABLE

reviews_by_month_count_df = queries.get_reviews_by_month_count_df(pg_query.engine)
reviews_by_month_count_df_after_2015 = reviews_by_month_count_df[
    (reviews_by_month_count_df["year_month"] > "2015-01-01")
    & (
        reviews_by_month_count_df["year_month"]
        < reviews_by_month_count_df["year_month"].max()
    )
]

if VERBOSE:
    display(reviews_by_month_count_df.dtypes)

importlib.reload(graph)

fig = graph.render_fig_reviews_by_month_count_after_2015(
    reviews_by_month_count_df_after_2015
)
graph_utils.render_fig(fig)

importlib.reload(stats_utils)

df = reviews_by_month_count_df.copy()[["year_month", "prop_top_1", "total_reviews"]]
df = df.sort_values("year_month")

df["year_month"] = pd.to_datetime(df["year_month"])
df["prop_bottom_99"] = 1 - df["prop_top_1"]
df["growth_rate_top_1"] = (df["total_reviews"] * df["prop_top_1"]).pct_change()
df["growth_rate_bottom_99"] = (
    df["total_reviews"] * (1 - df["prop_top_1"])
).pct_change()

df_after2020 = reviews_by_month_count_df[
    (reviews_by_month_count_df["year_month"] > "2020-01-01")
    & (
        reviews_by_month_count_df["year_month"]
        < reviews_by_month_count_df["year_month"].max()
    )
]

mean_top_1 = df["growth_rate_top_1"].mean()
std_top_1 = df["growth_rate_top_1"].std()

mean_bottom_99 = df["growth_rate_bottom_99"].mean()
std_bottom_99 = df["growth_rate_bottom_99"].std()

# Normality
shapiro_test_stat, shapiro_p_val = shapiro(df["growth_rate_top_1"].dropna())

# Homogeneity of Variances
levene_test_stat, levene_p_val = levene(
    df["growth_rate_top_1"].dropna(), df["growth_rate_bottom_99"].dropna()
)
u_stat, p_val_mannwhitney = mannwhitneyu(
    df["growth_rate_top_1"].dropna(), df["growth_rate_bottom_99"].dropna()
)

avg_top_1 = df["growth_rate_top_1"].mean()
avg_bottom_99 = df["growth_rate_bottom_99"].mean()

display(
    Markdown(
        f"""
**Growth rate for top 1%:**  
Mean: {round(mean_top_1, 2)} (Std Dev: {round(std_top_1, 2)})

**Growth rate for bottom 99%:**  
Mean: {round(mean_bottom_99, 2)} (Std Dev: {round(std_bottom_99, 2)})

To decide the appropriate test, the following should be considered:  
1. Data should be normally distributed or the sample size should be large.
2. Variances of the two groups being compared should be equal.

If these assumptions do not hold, a non-parametric test like the **Mann-Whitney U Test** should be used.

**Shapiro-Wilk Test for Normality:**  
Test Stat: {round(shapiro_test_stat, 2)}  
P-value: {round(shapiro_p_val, 2)} (If p-value < 0.05, data is not normally distributed).  
This indicates that a non-parametric test should be used.

**Levene Test for Homogeneity of Variances:**  
Test Stat: {round(levene_test_stat, 2)}  
P-value: {round(levene_p_val, 2)}  
This also supports the decision to use a non-parametric test.

**Mann-Whitney U Test:**  
U-value: {round(levene_test_stat, 2)}  
P-value: {round(levene_p_val, 2)}

The p-value indicates a significant difference in growth rates between the top 1% and bottom 99%. This mean we can reject the null hypothesis, which would support our initial hypothesis that the popularity of the top 1% most popular podcast has grown at a significantly faster pace than the rest.

**Comparison of Average Growth Rates:**  
Average growth for top 1%: {round(avg_top_1, 2)}  
Average growth for the bottom 99%: {round(avg_bottom_99, 2)}
"""
    )
)

importlib.reload(pg_query)
df_podcasts_by_rev_count = queries.get_podcasts_by_review_count(pg_query.engine);

df_podcasts_by_rev_count.columns

Index(['podcast_id', 'title', 'num_reviews', 'categories'], dtype='object')

importlib.reload(graph)

df_podcasts_by_rev_count = df_podcasts_by_rev_count.sort_values(
    by="num_reviews"
)  # Sort
df_podcasts_by_rev_count["cumulative_share"] = (
    df_podcasts_by_rev_count["num_reviews"].cumsum()
    / df_podcasts_by_rev_count["num_reviews"].sum()
)

fig, gini = graph.render_fig_lorenz_curve(df_podcasts_by_rev_count)
graph_utils.render_fig(fig)

print(f"Gini coefficient is: {gini}")

Gini coefficient is: 0.93

total_reviews = df_podcasts_by_rev_count['num_reviews'].sum()

percentiles = [0.01, 0.05, 0.10, 0.50]
thresholds = df_podcasts_by_rev_count['num_reviews'].quantile([1 - p for p in percentiles])

proportions = {}
for percentile, threshold in zip(percentiles, thresholds):
    proportion = df_podcasts_by_rev_count.loc[df_podcasts_by_rev_count['num_reviews'] >= threshold, 'num_reviews'].sum()
    proportions[f'Top {percentile*100:.0f}%'] = proportion

total_reviews = df_podcasts_by_rev_count['num_reviews'].sum()
proportions = {k: round((v / total_reviews) * 100, 1) for k, v in proportions.items()}
proportions_df = pd.DataFrame(list(proportions.items()), columns=['Percentile', 'Proportion (%)'])

display(Markdown("Distribution of podcast reviews by percentile (e.g. top 5% of all podcasts have 71.2% of all reviews):"))
print(proportions_df)

  Percentile  Proportion (%)
0     Top 1%            43.1
1     Top 5%            71.2
2    Top 10%            81.5
3    Top 50%            98.1

importlib.reload(queries)
user_reviews_data_df = queries.get_user_reviews_data(pg_query.engine)
if VERBOSE:
    display(user_reviews_data_df);

display(
    Markdown(
        f"""Distribution of review count by user:
        
mean: {user_reviews_data_df["review_count"].mean():.2f}
median: {user_reviews_data_df["review_count"].median():.2f}
max: {user_reviews_data_df["review_count"].max():.0f}

stdev: {user_reviews_data_df["review_count"].std():.2f}
skewness*: {user_reviews_data_df['review_count'].skew():.2f}
*is extremely high and indicates a very strong rightward skewness. This suggests that most of the data values are clustered around the left, with a few extremely large values on the right.*

kurtosis**: {user_reviews_data_df['review_count'].kurtosis():.2f}

*direction and degree of asymmetry. A positive skew indicates that the tail is on the right side of the distribution.
** high kurtosis means more of the variance is the result of infrequent extreme deviations.
"""
    )
)

bins = [0, 2, 3, 6, 9999999]
labels = ["1", "1-2", "2-5", "5+"]

user_reviews_data_df["bin"] = pd.cut(
    user_reviews_data_df["review_count"], bins=bins, labels=labels, right=False
)
user_reviews_data_df["other_reviews"] = (
    user_reviews_data_df["review_count"]
    - user_reviews_data_df["top_percentile_review_count"]
)

bin_counts = user_reviews_data_df["bin"].value_counts().sort_index()

total_users = len(user_reviews_data_df)
proportions = (bin_counts / total_users) * 100
proportions_text = [f"{p:.2f}%" for p in proportions]

top_review_bin_counts = user_reviews_data_df.groupby("bin")[
    "top_percentile_review_count"
].sum()
other_review_bin_counts = user_reviews_data_df.groupby("bin")["other_reviews"].sum()

total_bin_counts = user_reviews_data_df.groupby("bin")["review_count"].sum()

top_proportions = (top_review_bin_counts / total_bin_counts) * 100
other_proportions = (other_review_bin_counts / total_bin_counts) * 100

top_proportions_text = [f"{p:.2f}%" for p in top_proportions]
other_proportions_text = [f"{p:.2f}%" for p in other_proportions]

fig = go.Figure(
    data=[
        go.Bar(
            name="Other Reviews",
            x=labels,
            y=other_review_bin_counts.values,
            text=other_proportions_text,
            textposition="auto",
            textfont=dict(size=12),
        ),
        go.Bar(
            name="Top Percentile Reviews",
            x=labels,
            y=top_review_bin_counts.values,
            text=top_proportions_text,
            textposition="auto",
            textfont=dict(size=12),
        ),
    ]
)

fig.update_layout(
    barmode="stack",
    yaxis_title="Number of Users (Log Scale)",
    xaxis_title="Review Count Range",
    title="Distribution of User Reviews",
)

graph_utils.render_fig(fig)

if VERBOSE:
    display(user_reviews_data_df["first_review_is_top"].value_counts())
    display(user_reviews_data_df.columns)

# Calculate unique users per quarter
user_reviews_data_df["first_review_month"] = pd.to_datetime(
    user_reviews_data_df["first_review_month"]
)

user_reviews_data_df_after_2015 = user_reviews_data_df[
    (user_reviews_data_df["first_review_month"] > "2015-01-01")
    & (
        user_reviews_data_df["first_review_month"]
        < user_reviews_data_df["first_review_month"].max()
    )
]

unique_users_by_quarter = (
    user_reviews_data_df_after_2015.groupby(
        [
            user_reviews_data_df_after_2015["first_review_month"].dt.to_period("Q"),
            "first_review_is_top",
        ]
    )["author_id"]
    .nunique()
    .reset_index()
)
unique_users_by_quarter.columns = ["Quarter", "first_review_is_top", "Unique_Users"]
unique_users_by_quarter["Quarter"] = unique_users_by_quarter["Quarter"].astype(str)


fig = px.bar(
    unique_users_by_quarter,
    x="Quarter",
    y="Unique_Users",
    color="first_review_is_top",
    title="New Monthly Users by First Reviewed Podcast",
)

fig.update_xaxes(tickangle=-45)
fig.update_layout(legend_title_text="First reviewed podcast in top 1%")
fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)

graph_utils.render_fig(fig)

cutoff_date = pd.to_datetime('2020-01-01')
user_reviews_data_df['first_review_month'] = pd.to_datetime(user_reviews_data_df['first_review_month'])
user_reviews_data_df['last_review_month'] = pd.to_datetime(user_reviews_data_df['last_review_month'])
max_date = user_reviews_data_df['last_review_month'].max()
six_months_ago = max_date - pd.DateOffset(months=6)

filtered_df = user_reviews_data_df[user_reviews_data_df['first_review_month'] <= six_months_ago]

old_users = filtered_df[filtered_df['first_review_month'] < cutoff_date]
new_users = filtered_df[filtered_df['first_review_month'] >= cutoff_date]

u_statistic, p_value = mannwhitneyu(old_users['first_six_months_review_count'], new_users['first_six_months_review_count'], alternative='two-sided')

print(f'U statistic: {round(u_statistic, 2)}')
print(f'P-value: {round(p_value, 2)}')

if p_value < 0.05:
    # If the p-value is less than 0.05, we reject the null hypothesis.
    print("There is a significant difference in the number of reviews left during the first six months between the two groups.")
else:
    print("There is no significant difference in the number of reviews left during the first six months between the two groups.")

old_users_mean = old_users['first_six_months_review_count'].mean()
old_users_std = old_users['first_six_months_review_count'].std()

# Calculate the mean and standard deviation for new users
new_users_mean = new_users['first_six_months_review_count'].mean()
new_users_std = new_users['first_six_months_review_count'].std()

# Output the mean and standard deviation for both groups
print(f'Old Users - Mean: {old_users_mean:.2f}, Std Dev: {old_users_std:.2f}, Count: {len(old_users)}')
print(f'After 2020 Users - Mean: {new_users_mean:.2f}, Std Dev: {new_users_std:.2f}, Count: {len(new_users)}')

U statistic: 251977982392.5
P-value: 0.0
There is a significant difference in the number of reviews left during the first six months between the two groups.
Old Users - Mean: 1.13, Std Dev: 0.98, Count: 773917
After 2020 Users - Mean: 1.12, Std Dev: 0.99, Count: 648280

old_users['bin'] = pd.cut(old_users['first_six_months_review_count'], bins=bins, labels=labels, right=False)
new_users['bin'] = pd.cut(new_users['first_six_months_review_count'], bins=bins, labels=labels, right=False)

old_user_bin_counts = old_users['bin'].value_counts().sort_index()
new_user_bin_counts = new_users['bin'].value_counts().sort_index()

old_user_proportions = (old_user_bin_counts / old_users['bin'].count()) * 100
new_user_proportions = (new_user_bin_counts / new_users['bin'].count()) * 100

fig = go.Figure(data=[
    go.Bar(
        name='Old Users',
        x=labels,
        y=old_user_proportions.values,
        text=[f"{p:.2f}%" for p in old_user_proportions],
        textposition='auto'
    ),
    go.Bar(
        name='New Users',
        x=labels,
        y=new_user_proportions.values,
        text=[f"{p:.2f}%" for p in new_user_proportions],
        textposition='auto'
    )
])

fig.update_layout(
    barmode='group',
    yaxis=dict(
        title='Percentage of Total Users in Group',
        ticksuffix='%'
    ),
    xaxis_title='Review Count Range in First 6 Months',
    title='Comparison of Review Count Proportions Between Users Who Have Joined Prior to 2020 and After'
)

graph_utils.render_fig(fig)

# Each podcast might belong to many categories, so we'll get a table with all podcast/category pairs
importlib.reload(queries)

pod_cat_pairs = queries.get_pod_cat_pairs(pg_query.engine)
if VERBOSE:
    display(pod_cat_pairs)

TOP_N = 20

# Podcasts might contain multiple categories like 'religion-spirituality' and 'religion', we want to look at only
# the top level category. So let's select the top level category only and drop all duplicate category entries for
# each podcast.

def get_top_level_category(pod_cat_pairs):
    pod_cat_pairs["top_level_category"] = (
        pod_cat_pairs["category"].str.split("-").str[0]
    )
    pod_cat_pairs_top_lvl_cat = pod_cat_pairs.drop_duplicates(
        ["podcast_id", "top_level_category"]
    )

    # 'true' is ambiguous keep true crime
    pod_cat_pairs_top_lvl_cat.loc[
        pod_cat_pairs_top_lvl_cat["category"].str.startswith("true-crime"),
        "top_level_category",
    ] = "true-crime"
    return pod_cat_pairs_top_lvl_cat


total_unique_podcasts = pod_cat_pairs["podcast_id"].nunique()

pod_cat_pairs_top_lvl_cat = get_top_level_category(pod_cat_pairs)
pod_cat_pairs_top_lvl_cat = pod_cat_pairs_top_lvl_cat.drop_duplicates(
    ["podcast_id", "top_level_category"]
)

def top_categories_with_unique_podcasts(data, col, n):
    category_stats = (
        data.groupby(col)
        .agg(
            review_count=pd.NamedAgg(column="review_count", aggfunc="sum"),
            unique_podcasts=pd.NamedAgg(column="podcast_id", aggfunc="nunique"),
        )
        .reset_index()
    )
    category_stats = category_stats.sort_values(by="review_count", ascending=False)
    top_categories = category_stats.head(n)

    return top_categories


category_counts = top_categories_with_unique_podcasts(
    pod_cat_pairs_top_lvl_cat, "category", TOP_N
)
category_counts_top_cat = top_categories_with_unique_podcasts(
    pod_cat_pairs_top_lvl_cat, "top_level_category", TOP_N
)

if VERBOSE:
    display(category_counts)
    display(category_counts_top_cat)
    display(pod_cat_pairs_top_lvl_cat.columns)
    display(category_counts_top_cat["top_level_category"].unique())

def add_prctile_group(df):
    # Calculate the 1% and 5% thresholds for each top_level_category
    thresholds_1 = df.groupby("top_level_category")["review_count"].quantile(0.99)
    thresholds_5 = df.groupby("top_level_category")["review_count"].quantile(0.95)

    def label_percentile(row):
        threshold_1 = thresholds_1.get(row["top_level_category"], 0)
        threshold_5 = thresholds_5.get(row["top_level_category"], 0)
        if row["review_count"] > threshold_1:
            return "Top 1%"
        elif row["review_count"] > threshold_5:
            return "1-5%"
        else:
            return "Bottom 95%"

    # Apply the labeling function
    df["percentile_group"] = df.apply(label_percentile, axis=1)
    return df

filtered_dataframe = pod_cat_pairs_top_lvl_cat[
    pod_cat_pairs_top_lvl_cat["top_level_category"].isin(
        category_counts_top_cat["top_level_category"]
    )
]

if VERBOSE:
    display(category_counts_top_cat["top_level_category"].unique())
    display(filtered_dataframe["top_level_category"].unique())

add_prctile_group(filtered_dataframe)

sum_df = (
    filtered_dataframe.groupby(["top_level_category", "percentile_group"])[
        "review_count"
    ]
    .sum()
    .reset_index()
)

sorted_categories = (
    sum_df.groupby("top_level_category")["review_count"]
    .sum()
    .sort_values(ascending=False)
    .index.tolist()
)

fig = px.bar(
    sum_df,
    x="top_level_category",
    y="review_count",
    color="percentile_group",
    title="Review Count by Top Level Category and Percentile Group",
    labels={"review_count": "Review Count", "top_level_category": "Top Level Category"},
    barmode="stack",
    category_orders={
        "top_level_category": sorted_categories,
        "percentile_group": ["Bottom 95%", "1-5%", "Top 1%"],
    },
)

total_reviews = sum_df.groupby("top_level_category")["review_count"].sum().to_dict()

for data in fig.data:
    proportions = [
        (count / total_reviews[category]) * 100
        for count, category in zip(data["y"], data["x"])
    ]
    data["text"] = [f"{p:.0f}%" for p in proportions]
    data["textposition"] = "auto"

fig.update_layout(legend_title_text="Distribution of reviews for podcasts in category")
fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)

graph_utils.render_fig(fig);

pod_cat_pairs_top_lvl_cat = add_prctile_group(pod_cat_pairs_top_lvl_cat)
podcast_count = (
    pod_cat_pairs_top_lvl_cat.groupby("top_level_category")["podcast_id"]
    .nunique()
    .reset_index(name="num_podcasts")
)

top_1_percent_reviews = pod_cat_pairs_top_lvl_cat[
    pod_cat_pairs_top_lvl_cat["percentile_group"] == "Top 1%"
]
top_1_percent_sum = (
    top_1_percent_reviews.groupby("top_level_category")["review_count"]
    .sum()
    .reset_index(name="top_1_percent_reviews")
)

total_reviews = (
    pod_cat_pairs_top_lvl_cat.groupby("top_level_category")["review_count"]
    .sum()
    .reset_index(name="total_reviews")
)

merged_df = pd.merge(
    podcast_count, top_1_percent_sum, on="top_level_category", how="outer"
)
merged_df = pd.merge(merged_df, total_reviews, on="top_level_category", how="outer")
merged_df = merged_df[merged_df["total_reviews"] >= 20000]
merged_df["proportion_top_1"] = (
    merged_df["top_1_percent_reviews"] / merged_df["total_reviews"]
)

fig = px.scatter(
    merged_df,
    x="proportion_top_1",
    y="total_reviews",
    text="top_level_category",
    size="num_podcasts",
    labels={
        "proportion_top_1": "Proportion of Reviews in Top 1%",
        "total_reviews": "Total Review Count",
    },
    title="Proportion of Top 1% Reviews vs Total Review Count",
)

fig.update_traces(textposition="top center")

graph_utils.render_fig(fig)

if VERBOSE:
    pod_cat_pairs_top_lvl_cat
    pod_cat_pairs_top_lvl_cat["percentile_group"].value_counts()

top_categories = category_counts_top_cat["top_level_category"]
top_podcasts_df = pod_cat_pairs_top_lvl_cat[
    pod_cat_pairs_top_lvl_cat["top_level_category"].isin(top_categories)
]

top_podcasts_df["top_1_prct"] = top_podcasts_df["percentile_group"].map(
    lambda l: l == "Top 1%"
)

review_changes = (
    top_podcasts_df.groupby(["top_level_category", "top_1_prct"])
    .agg({"reviews_2018_2020": "sum", "reviews_2020_2022": "sum"})
    .reset_index()
)

review_changes["percent_change"] = (
    review_changes["reviews_2020_2022"] - review_changes["reviews_2018_2020"]
) / review_changes["reviews_2018_2020"]
review_changes["total_reviews_sum"] = (
    review_changes["reviews_2018_2020"] + review_changes["reviews_2020_2022"]
)
sorted_review_changes = review_changes.sort_values(
    by="total_reviews_sum", ascending=False
)

fig = px.bar(
    sorted_review_changes,
    x="top_level_category",
    y="percent_change",
    color="top_1_prct",  # Color based on the 'top_1_prct' group
    title="Percentage Change in Number of Podcast Reviews for Top Categories",
    labels={"percent_change": "Percentage Change", "top_level_category": "Category"},
    barmode="group",
    hover_data=["reviews_2018_2020", "reviews_2020_2022"],
)

fig.update_traces(
    hovertemplate="<br>".join(
        [
            "Category: %{x}",
            "Reviews 2018-2020: %{customdata[0]:,}",
            "Reviews 2020-2022: %{customdata[1]:,}",
            "Percent Change: %{y:.2%}",
        ]
    )
)


fig.update_layout(
    yaxis_tickformat=".0%",
    yaxis_title="Percent Change in Review Count",
    xaxis_tickangle=-45,
)
fig.update_layout(legend_title_text="Top 1% Podcasts")
fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)

graph_utils.render_fig(fig)

Background¶

Introduction¶

Popularity and average top 10% percentile count over time¶

Did most of the growth went to the top 1% of podcasts¶

Hypothesis I¶

Distribution of Podcasts by Popularity:¶

User Engagement Analysis¶

Hypothesis II¶

Podcast Genre/Genre Analysis¶

Summary¶